package com.gec.dao.impl;

import com.gec.dao.EmployeeDao;
import com.gec.domain.Dept;
import com.gec.domain.Employee;
import com.gec.domain.Job;
import com.gec.utils.JDBCUtils;
import com.gec.utils.PageModel;

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao {
    //添加员工信息
    @Override
    public void addEmployee(Employee employee) {
        String sql="INSERT INTO employee_inf(id,NAME,card_id,address,POST_CODE,tel,phone,QQ_num,email,sex,party,birthday,race,education,speciality,hobby,remark,create_date,dept_id,job_id)\n" +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[]params={employee.getId(),employee.getName(),employee.getCARD_ID(),employee.getAddress(),
                employee.getPostCode(),employee.getTel(),employee.getPhone(),employee.getQQ_NUM(),
                employee.getEmail(),employee.getSex(),employee.getParty(),employee.getBirthday(),employee.getRace(),
                employee.getEducation(),employee.getSpeciality(),employee.getHobby(),employee.getRemark(),
                employee.getcreate_date(),employee.getDept().getId(),employee.getJob().getId()};

        try {
            JDBCUtils.executeUpdate(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
    }

    //删除员工信息
    @Override
    public void deleteEmployee(int id) {
        String sql="DELETE FROM employee_inf WHERE id=?";
        Object[]params={id};
        try {
            JDBCUtils.executeUpdate(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
    }

    //修改员工信息
    @Override
    public void updateEmployee(Employee employee) {
        String sql="UPDATE employee_inf SET NAME=?,card_id=?,address=?,POST_CODE=?,tel=?,phone=?,QQ_num=?,email=?,sex=?,party=?,birthday=?,race=?,education=?,speciality=?,hobby=?,remark=?,create_date=?,dept_id=?,job_id=? WHERE id=?";
        System.out.println(employee.getQQ_NUM());
        Object[]params={employee.getName(),employee.getCARD_ID(),employee.getAddress(),
                employee.getPostCode(),employee.getTel(),employee.getPhone(),employee.getQQ_NUM(),
                employee.getEmail(),employee.getSex(),employee.getParty(),employee.getBirthday(),employee.getRace(),
                employee.getEducation(),employee.getSpeciality(),employee.getHobby(),employee.getRemark(),
                employee.getcreate_date(),employee.getDept().getId(),employee.getJob().getId(),employee.getId()};
        try{
            JDBCUtils.executeUpdate(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
    }

    //查询所有员工信息
    @Override
    public List<Employee> findAllemployees(Employee employee, PageModel model) {
        List<Employee>employees=new ArrayList<>();
//        //动态查询
        StringBuffer sql=new StringBuffer(" SELECT * FROM employee_inf " +
                " LEFT JOIN dept_inf ON employee_inf.`dept_id`=dept_inf.`ID` " +
                " LEFT JOIN job_inf ON employee_inf.`job_id`=job_inf.`ID` where 1=1 ");
       List<Object>objects=new ArrayList<>();
        ResultSet rs=null;

        try{
            //防止空指针异常 利用员工的姓名去判断
            if(employee !=null && !"".equals(employee.getName()) && employee.getName() !=null){
                //拼接数据库
                sql.append(" and employee_inf.name like ?");
                objects.add("%"+employee.getName()+"%");
            }

            //利用员工的性别去判断
//            if(employee !=null && !"".equals(employee.getSex()) && employee.getSex() !=null){
//                sql.append(" and employee_inf.sex like ?");
//                objects.add("%"+employee.getSex()+"%");
//            }
            //利用员工的身份证去判断
            if (employee !=null && !"".equals(employee.getCARD_ID()) && employee.getCARD_ID() != null){
                sql.append(" and employee_inf.CARD_ID like ?");
                objects.add("%"+employee.getCARD_ID()+"%");

            }
            //利用员工的手机号去判断
            if(employee !=null && !"".equals(employee.getPhone()) && employee.getPhone() !=null){
                sql.append("  and employee_inf.phone like ?");
                objects.add("%"+employee.getPhone()+"%");
            }
            //利用员工的职位去判断
            if(employee !=null && !"".equals(employee.getJob().getId()) && employee.getJob().getId()!=null){
                sql.append(" and Job_inf.ID=?");
                objects.add(employee.getJob().getId());
            }
            //利用员工的部门去判断
            if(employee !=null && !"".equals(employee.getDept().getId()) && employee.getDept().getId()!=null){
                sql.append(" and Dept_inf.ID=?");
                objects.add(employee.getDept().getId());
            }

            //形成分页查询
            sql.append(" limit ").append(model.getStartRow()).append(",").append(PageModel.pageSize);
            //形成数组形式
            Object[]params=objects.toArray(new Object[objects.size()]);

            rs=JDBCUtils.executeQuery(sql.toString(),params);
            while(rs.next()){
                Employee employee1=new Employee();
                copy(rs,employee1);
                employees.add(employee1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
        return employees;
    }

    //获得总的员工数目
    @Override
    public int getTotalEmployeeCount(Employee employee) {
        StringBuffer sql=new StringBuffer(" SELECT count(0) FROM employee_inf " +
                " LEFT JOIN dept_inf ON employee_inf.`dept_id`=dept_inf.`ID` " +
                " LEFT JOIN job_inf ON employee_inf.`job_id`=job_inf.`ID` where 1=1 ");

        List<Object>objects=new ArrayList<>();
        ResultSet rs=null;
        try {
            //防止空指针异常 利用员工的姓名去判断
            if(employee !=null && !"".equals(employee.getName()) && employee.getName() !=null){
                //拼接数据库
                sql.append(" and employee_inf.name like ?");
                objects.add("%"+employee.getName()+"%");
            }

            //利用员工的性别去判断
//            if(employee !=null && !"".equals(employee.getSex()) && employee.getSex() !=null){
//                sql.append(" and employee_inf.sex like ?");
//                objects.add("%"+employee.getSex()+"%");
//            }
            //利用员工的身份证去判断
            if (employee !=null && !"".equals(employee.getCARD_ID()) && employee.getCARD_ID() != null){
                sql.append(" and employee_inf.CARD_ID like ?");
                objects.add("%"+employee.getCARD_ID()+"%");

            }
            //利用员工的手机号去判断
            if(employee !=null && !"".equals(employee.getPhone()) && employee.getPhone() !=null){
                sql.append("  and employee_inf.phone like ?");
                objects.add("%"+employee.getPhone()+"%");
            }
            //利用员工的职位去判断
            if(employee !=null && !"".equals(employee.getJob().getId()) && employee.getJob().getId()!=null){
                sql.append(" and Job_inf.ID=?");
                objects.add(employee.getJob().getId());
            }
            //利用员工的部门去判断
            if(employee !=null && !"".equals(employee.getDept().getId()) && employee.getDept().getId()!=null){
                sql.append(" and Dept_inf.ID = ?");
                objects.add(employee.getDept().getId());
            }
            Object[]params=objects.toArray(new Object[objects.size()]);
            rs=JDBCUtils.executeQuery(sql.toString(),params);
            while(rs.next()){
                return  rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
        return 0;
    }

    //查询个人信息
    @Override
    public Employee findById(int id) {
        //String sql="SELECT * FROM employee_inf WHERE id=?";
        String sql="SELECT * FROM employee_inf \n" +
                "LEFT JOIN dept_inf ON employee_inf.`dept_id`=dept_inf.`ID`\n" +
                "LEFT JOIN job_inf ON employee_inf.`job_id`=job_inf.`ID` WHERE 1=1 AND employee_inf.id=?";
        Object[]params={id};
        ResultSet rs=null;
        try {
            rs=JDBCUtils.executeQuery(sql,params);
            while(rs.next()){
                Employee employee1=new Employee();
                copy(rs,employee1);
                return employee1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private void copy(ResultSet rs, Employee employee1) {
        try{
            employee1.setId(rs.getInt("id"));
            employee1.setName(rs.getString("name"));
            employee1.setCARD_ID(rs.getString("CARD_ID"));
            employee1.setAddress(rs.getString("address"));
            employee1.setPOST_CODE(rs.getString("POST_CODE"));
            employee1.setTel(rs.getString("tel"));
            employee1.setPhone(rs.getString("phone"));
            employee1.setQQ_NUM(rs.getString("QQ_NUM"));
            employee1.setEmail(rs.getString("email"));
            employee1.setSex(rs.getInt("sex"));
            employee1.setParty(rs.getString("party"));
            employee1.setBirthday(rs.getDate("birthday"));
            employee1.setRace(rs.getString("race"));
            employee1.setEducation(rs.getString("education"));
            employee1.setSpeciality(rs.getString("speciality"));
            employee1.setHobby(rs.getString("hobby"));
            employee1.setRemark(rs.getString("remark"));
            employee1.setcreate_date(rs.getDate("create_date"));

            employee1.setDEPT_ID(rs.getInt(20));
            employee1.setJOB_ID(rs.getInt(21));
            //获取字段数量 解决
            int columnCount = rs.getMetaData().getColumnCount();

            //获取部门
            final Dept dept=new Dept();
            //获取职位
            final Job job=new Job();
            //如果字段大于21，才会执行
            if(columnCount>21) {
                dept.setId(rs.getInt(22));
                dept.setName(rs.getString(23));
                dept.setRemark(rs.getString(24));
                dept.setState(rs.getInt(25));
                job.setId(rs.getInt(26));
                job.setName(rs.getString(27));
                job.setRemark(rs.getString(28));
                job.setState(rs.getInt(29));
            }
            employee1.setDept(dept);
            employee1.setJob(job);

        } catch (SQLException e) {
            e.printStackTrace();
        }


    }
}
